﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;

/**
 * シール報告管理明細パーシスター
 */
namespace Kaidou.Persister
{
    public class TRN_Report_Seal_Persister:Common
    {
        public bool insertByProcedure(SqlDB db, TrnReportd model)
        {
            try
            {
                string in_report_no = model.Id.ReportNo;
                short in_report_rec_no = model.Id.ReportRecNo;
                DateTime nowTime_cur = System.DateTime.Now;
                DateTime wk_reg_date = nowTime_cur;
                DateTime wk_upd_date = nowTime_cur;
                string wk_rh_union_code = "";
                short wk_rh_branch_code = 0;
                string wk_rh_report_tanto_name = "";
                string wk_rh_trader_reg_no = "";
                string wk_rh_upd_union_code = "";
                short wk_rh_upd_branch_code = 0;

                string wk_rd_seal_str_no = "";
                string wk_rd_seal_end_no = "";
                string wk_rd_machine_clss = "";
                string wk_dup_error_flg = "";

                string wk_ps_seal_no = "";
                int wk_ps_report_qty = 0;
                string wk_ps_juchu_no = "";

                string sql1 = "SELECT union_code,branch_code,report_tanto_name,trader_reg_no,upd_union_code, upd_branch_code FROM trn_report_h AS trn_report_h WHERE report_no ='" + in_report_no + "'";
                string sql2 = "SELECT seal_str_no,seal_end_no,machine_clss FROM trn_report_d AS trn_report_d WHERE report_no = '" + in_report_no + "' and report_rec_no = " + in_report_rec_no;
                //string sql3 = "SELECT seal_no,report_qty FROM trn_purch_seal AS trn_purch_seal WHERE seal_no = wk_seal_no";

                SqlParameter[] sqlParameter = { };
                SqlDataReader dataReader = db.GetDataCmd(sql1, sqlParameter);
                if (dataReader.Read())
                {
                    wk_rh_union_code = (string)dataReader["union_code"];
                    wk_rh_branch_code = (short)dataReader["branch_code"];
                    wk_rh_report_tanto_name = (string)dataReader["report_tanto_name"];
                    wk_rh_trader_reg_no = (string)dataReader["trader_reg_no"];
                    wk_rh_trader_reg_no = wk_rh_trader_reg_no.Replace("-","");
                    wk_rh_trader_reg_no = wk_rh_trader_reg_no.Replace("-","");
                    wk_rh_upd_union_code = (string)dataReader["upd_union_code"];
                    wk_rh_upd_branch_code = (short)dataReader["upd_branch_code"];
                }
                dataReader.Close();

                dataReader = db.GetDataCmd(sql2, sqlParameter);
                if (dataReader.Read())
                {
                    wk_rd_seal_str_no = (string)dataReader["seal_str_no"];
                    wk_rd_seal_end_no = (string)dataReader["seal_end_no"];
                    wk_rd_machine_clss = (string)dataReader["machine_clss"];

                }
                dataReader.Close();
                string wk_report_no = in_report_no;
                short wk_report_rec_no = in_report_rec_no;
                string wk_union_code = wk_rh_union_code;
                short wk_branch_code = wk_rh_branch_code;
                string wk_trader_reg_no = wk_rh_trader_reg_no;
                string wk_upd_union_code = wk_rh_upd_union_code;
                short wk_upd_branch_code = wk_rh_upd_branch_code;

                string wk_machine_clss = wk_rd_machine_clss;
                string wk_report_seal_sts = "10";
                string wk_upd_func_id = "Insert_report_proc";
                int wk_rpt_seal_seqno = 0;
                string wk_dif_report_flg = "0";

                string wk_etc_seal_str_no = wk_rd_seal_str_no;
                string wk_etc_seal_end_no = wk_rd_seal_end_no;
                int wk_etc_start_no = int.Parse(wk_etc_seal_str_no);
                int wk_etc_end_no = 0;
                if (wk_etc_seal_end_no.Length != 9)
                {
                    wk_etc_end_no = wk_etc_start_no;
                }
                else
                {
                    wk_etc_end_no = int.Parse(wk_etc_seal_end_no);
                }


                for (int count = wk_etc_start_no; count <= wk_etc_end_no; count++)
                {
                    Double number = count;
                    string autoNum = number.ToString("#########");
                    string wk_seal_no = autoNum;
                    int wk_seal_no_id = int.Parse(wk_seal_no);
                    // シール購入管理明細テーブル（trn_purch_seal)内を検索し
                    // 報告件数が1以上の場合には「エラー」とする
                    string sql4 = "select count(*) from trn_purch_seal where seal_no='" + wk_seal_no + "'";
                    dataReader = db.GetDataCmd(sql4, sqlParameter);
                    int counttmp = 0;
                    if (dataReader.Read())
                    {
                        counttmp = (int)dataReader[0];
                    }
                    if (counttmp >= 1)
                    {
                        wk_dup_error_flg = "1";
                    }
                    else
                    {
                        wk_dup_error_flg = "0";
                    }

                    dataReader.Close();
                    //シール報告管理明細（trn_report_seal）へインサート
                    int sno = getSequenceNextVal(System.DateTime.Now.ToString("yy"), "RPT_SEAL_SEQNO_SEQ");
                    string sqlStr = "INSERT INTO trn_report_seal( " +
                                    "rpt_seal_seqno, " +
                                    "seal_no, " +
                                    "seal_no_id, " +
                                    "union_code, " +
                                    "branch_code, " +
                                    "trader_reg_no, " +
                                    "report_no, " +
                                    "report_reg_no, " +
                                    "dup_error_flg, " +
                                    "dif_report_flg, " +
                                    "machine_clss, " +
                                    "report_seal_sts, " +
                                    "reg_date, " +
                                    "upd_date, " +
                                    "upd_union_code, " +
                                    "upd_branch_code, " +
                                    "upd_func_id " +
                            ") VALUES ( @rpt_seal_seqno,@seal_no,@seal_no_id,@union_code,@branch_code,@trader_reg_no,@report_no,@report_rec_no,@dup_error_flg,@dif_report_flg,@machine_clss,@report_seal_sts,@reg_date,@upd_date,@upd_union_code,@upd_branch_code,@upd_func_id )";

                    SqlParameter[] sqlParametertemp = {   
new SqlParameter("@rpt_seal_seqno",SqlDbType.Int),         
new SqlParameter("@seal_no",SqlDbType.VarChar),       
new SqlParameter("@seal_no_id" ,SqlDbType.Int),       
new SqlParameter("@union_code" ,SqlDbType.VarChar),        
new SqlParameter("@branch_code" ,SqlDbType.SmallInt),       
new SqlParameter("@trader_reg_no" ,SqlDbType.VarChar), 
new SqlParameter("@report_no",SqlDbType.VarChar),     
new SqlParameter("@report_rec_no",SqlDbType.VarChar),    
new SqlParameter("@dup_error_flg",SqlDbType.VarChar),  
new SqlParameter("@dif_report_flg",SqlDbType.Int),       
new SqlParameter("@machine_clss",SqlDbType.VarChar),      
new SqlParameter("@report_seal_sts",SqlDbType.VarChar), 
new SqlParameter("@reg_date",SqlDbType.DateTime),          
new SqlParameter("@upd_date",SqlDbType.DateTime),          
new SqlParameter("@upd_union_code",SqlDbType.VarChar),    
new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),   
new SqlParameter("@upd_func_id",SqlDbType.VarChar)
            };

                    sqlParametertemp[0].Value = sno;
                    sqlParametertemp[1].Value = wk_seal_no;
                    sqlParametertemp[2].Value = wk_seal_no_id;
                    sqlParametertemp[3].Value = wk_union_code;
                    sqlParametertemp[4].Value = wk_branch_code;
                    sqlParametertemp[5].Value = wk_trader_reg_no.Substring(0,2)+"-"+wk_trader_reg_no.Substring(2,5);
                    sqlParametertemp[6].Value = wk_report_no;
                    sqlParametertemp[7].Value = wk_report_rec_no;
                    sqlParametertemp[8].Value = wk_dup_error_flg;
                    sqlParametertemp[9].Value = wk_dif_report_flg;
                    sqlParametertemp[10].Value = wk_machine_clss;
                    sqlParametertemp[11].Value = wk_report_seal_sts;
                    sqlParametertemp[12].Value = wk_reg_date;
                    sqlParametertemp[13].Value = wk_upd_date;
                    sqlParametertemp[14].Value = wk_upd_union_code;
                    sqlParametertemp[15].Value = wk_upd_branch_code;
                    sqlParametertemp[16].Value = wk_upd_func_id;

                    db.ExecuteCmd(sqlStr, sqlParametertemp);

                    string sql3 = "SELECT seal_no, report_qty FROM trn_purch_seal AS trn_purch_seal WHERE seal_no = '" + wk_seal_no + "'";
                    dataReader = db.GetDataCmd(sql3, sqlParameter);
                    if (dataReader.Read())
                    {
                        wk_ps_seal_no = (string)dataReader["seal_no"];
                        wk_ps_report_qty = (int)dataReader["report_qty"];
                    }
                    dataReader.Close();
                    wk_ps_report_qty = wk_ps_report_qty + 1;
                    sqlStr =
                            "UPDATE " +
                                    "trn_purch_seal " +
                            "SET " +
                                    "report_qty = @report_qty," +
                                    "upd_date = @upd_date," +
                                    "upd_union_code = @upd_union_code, " +
                                    "upd_branch_code = @upd_branch_code, " +
                                    "upd_func_id = @upd_func_id" +
                            " WHERE " +
                                    "seal_no = @seal_no";

                    SqlParameter[] sqlParametertemp2 = {         
new SqlParameter("@seal_no",SqlDbType.VarChar), 
new SqlParameter("@report_qty",SqlDbType.Int),          
new SqlParameter("@upd_date",SqlDbType.DateTime),          
new SqlParameter("@upd_union_code",SqlDbType.VarChar),    
new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),   
new SqlParameter("@upd_func_id",SqlDbType.VarChar)
            };
                    sqlParametertemp2[0].Value = wk_ps_seal_no;
                    sqlParametertemp2[1].Value = wk_ps_report_qty;
                    sqlParametertemp2[2].Value = wk_upd_date;
                    sqlParametertemp2[3].Value = wk_upd_union_code;
                    sqlParametertemp2[4].Value = wk_upd_branch_code;
                    sqlParametertemp2[5].Value = wk_upd_func_id;
                    db.ExecuteCmd(sqlStr, sqlParametertemp2);

                }
            }catch(Exception exx){
                LogManager.error("TRN_REPORT_Seql_Persister "+exx.Message+exx.StackTrace);
                //return false;
                throw new Exception("TRN_REPORT_Seql_Persister pro  error");
            }
            return true;

        }

        public bool deleteByProcedure( SqlDB db ,TrnReportd model)
        {
            try
            {
                string in_report_no = model.Id.ReportNo;
                short in_report_rec_no = model.Id.ReportRecNo;
                string wk_ps_upd_union_code = model.UpdUnionCode;
                short wk_ps_upd_branch_code = model.UpdBranchCode;
                string wk_ps_upd_func_id = "Delete_report_s_proc";
                string wk_ps_seal_no = "";
                int wk_ps_report_qty = 0;
                DateTime wk_ps_upd_date = System.DateTime.Now;

                string sql1 = "SELECT seal_no FROM trn_report_seal AS trn_report_seal WHERE report_no = '" + in_report_no + "' and report_reg_no = '" + in_report_rec_no + "'";
                //SqlDB db = new SqlDB();
                SqlDataReader dataReader = db.GetDataCmd(sql1);
                ArrayList wk_rs_seal_noss = new ArrayList();
                while (dataReader.Read())
                {
                    wk_rs_seal_noss.Add((string)dataReader[0]);
                    
                }
                dataReader.Close();
                for (int i = 0; i < wk_rs_seal_noss.Count;i++ )
                {
                    string wk_rs_seal_no= wk_rs_seal_noss[i].ToString();
                    string sql2 = "SELECT seal_no, report_qty FROM trn_purch_seal AS trn_purch_seal WHERE seal_no = '" + wk_rs_seal_no + "'";
                    SqlDataReader dataReader2 = db.GetDataCmd(sql2);
                    if (dataReader2.Read())
                    {
                        wk_ps_seal_no = (string)dataReader2["seal_no"];
                        wk_ps_report_qty = (int)dataReader2["report_qty"];
                    }
                    dataReader2.Close();
                    wk_ps_report_qty = wk_ps_report_qty - 1;
                    string sqlStr = "UPDATE  trn_purch_seal SET report_qty = @report_qty,upd_date = @upd_date,upd_union_code =@upd_union_code,upd_branch_code =@upd_branch_code,upd_func_id = @upd_func_id  WHERE seal_no = @seal_no";

                    SqlParameter[] sqlParametertemp = {   
new SqlParameter("@report_qty",SqlDbType.Int),         
new SqlParameter("@upd_date",SqlDbType.DateTime),       
new SqlParameter("@upd_union_code" ,SqlDbType.VarChar),
new SqlParameter("@upd_branch_code" ,SqlDbType.SmallInt),
new SqlParameter("@upd_func_id" ,SqlDbType.VarChar),
new SqlParameter("@seal_no" ,SqlDbType.VarChar)
                                                  };
                    sqlParametertemp[0].Value = wk_ps_report_qty;
                    sqlParametertemp[1].Value = wk_ps_upd_date;
                    sqlParametertemp[2].Value = wk_ps_upd_union_code;
                    sqlParametertemp[3].Value = wk_ps_upd_branch_code;
                    sqlParametertemp[4].Value = wk_ps_upd_func_id;
                    sqlParametertemp[5].Value = wk_ps_seal_no;
                    db.ExecuteCmd(sqlStr, sqlParametertemp);
                }

                string sqlStr2 = "DELETE FROM trn_report_seal WHERE report_no= '" + in_report_no + "' and report_reg_no='" + in_report_rec_no + "'";
                db.ExecuteCmd(sqlStr2,new SqlParameter[]{});
               // db.Close();
            }catch(Exception exx){
                LogManager.error("deleteByProcedure "+exx.Message+exx.StackTrace);
                return false;
            }
            return true;

        }
        
        /**
	    * intで受け取ったシール番号をStringに戻す
	    * decimal撲滅のため
	    */
	    private String getStrSealNo(int sealNo) {
		    string strSealNo = sealNo.ToString();
		    if (strSealNo.Length < 9) {
			    strSealNo = "0" + strSealNo;
		    }
		    return strSealNo;
	    }

        /**
	     * シール番号が既に登録されているかどうかをチェック。<br>
	     * 報告見出の修正時
	     * @param con コネクション
	     * @param seal_no_start int 確認シール番号開始
	     * @param seal_no_end   int 確認シール番号終了
	     * @param report_no String  報告見出番号
	     * @return int 自分以外報告されているレポートの数
	    */
	    public int countDuplicationReportForUpdate(int seal_no_start, int seal_no_end, string report_no) {
		
		    int count = 0;
		    string pssql = null;
            SqlDB db = new SqlDB();
            try
            {
                if (seal_no_end > 0)
                {
                    pssql = "select count(seal_no) as cnt " + "from trn_report_seal " + "where " + " report_no != '" + report_no + "' " + " and seal_no between '" + getStrSealNo(seal_no_start) + "' and '" + getStrSealNo(seal_no_end) + "' ";

                }
                else
                {

                    pssql = "select count(seal_no) as cnt " + "from trn_report_seal " + "where " + " report_no != '" + report_no + "' " + " and seal_no = '" + getStrSealNo(seal_no_start) + "' ";

                }

                LogManager.info("TRN_Report_Seal_Persister pssql " + pssql);

                SqlDataReader dataReader = db.GetDataCmd(pssql);

                if (dataReader.Read())
                {
                    count = (int)dataReader[0];
                }


            }
            catch (Exception ex)
            {
                LogManager.error("TRN_Report_Seal_Persister pssql " + ex.Message + ex.StackTrace);
            }
            finally
            {
                db.Close();
            }

		    return count;

	    }

        /*
         * 破棄諜報
         */
        public ArrayList sealReport(String seal_no_start, String seal_no_end)
        {
            ArrayList list = new ArrayList();
            SqlDB db = new SqlDB();
            try
            {
                string sql = "select a.office_name,b.upd_date from mst_union_view a,"
    + "(select upd_union_code,upd_branch_code,upd_date from trn_purch_seal where seal_no between '" + seal_no_start + "' and '" + seal_no_end + "' group by upd_union_code,upd_branch_code,upd_date) b"
    + " where a.union_code=b.upd_union_code and a.branch_code=b.upd_branch_code";
                LogManager.info("TRN_Report_Seal_Persister sql " + sql);

                SqlDataReader dataReader = db.GetDataCmd(sql);

                while (dataReader.Read())
                {
                    MstUnion m = new MstUnion();
                    m.OfficeName = (string)dataReader["office_name"];
                    m.UpdDate = (DateTime)dataReader["upd_date"];
                    list.Add(m);
                }



            }
            catch (Exception e1)
            {
                LogManager.error("TRN_Report_Seal_Persister sealReport " + e1.Message);

            }
            finally
            {
                db.Close();
            }
            return list;
        }
    }
}
